
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
alter  procedure [dbo].[p_finnewwagecalcfeebase] 
as  
begin
  set nocount on
  declare @iemptypeid integer, 
          @freeid integer,
          @ideptid integer,
          @ifilid integer,
          @icount integer;
  declare @local_tab table(emptypeid integer,
                           deptid integer,
                           feetypeid integer,
                           filid integer )

  insert into @local_tab
    select 1 as freeid,e.emptypeid,d.ED_DeptID,s.FI_FilID
    from td_dept d,td_emptype e,td_filiale s
      where (e.EmpTypeID Not In (Select EmptypeID From tfd_wagefeebase Where FreeID=1)) 
       Or (d.ED_DeptID Not In (Select DeptID From tfd_wagefeebase Where FreeID=1))
       Or (s.fi_filid Not In (Select filid From tfd_wagefeebase Where FreeID=1))
    union all
    select 2 as freeid,e.emptypeid,d.ED_DeptID,s.FI_FilID
    from td_dept d,td_emptype e,td_filiale s
      where (e.EmpTypeID Not In (Select EmptypeID From tfd_wagefeebase Where FreeID=2)) 
       Or (d.ED_DeptID Not In (Select DeptID From tfd_wagefeebase Where FreeID=2))
       Or (s.fi_filid Not In (Select filid From tfd_wagefeebase Where FreeID=2))
    union all
    select 3 as freeid,e.emptypeid,d.ED_DeptID,s.FI_FilID
    from td_dept d,td_emptype e,td_filiale s
      where (e.EmpTypeID Not In (Select EmptypeID From tfd_wagefeebase Where FreeID=3)) 
       Or (d.ED_DeptID Not In (Select DeptID From tfd_wagefeebase Where FreeID=3))
       Or (s.fi_filid Not In (Select filid From tfd_wagefeebase Where FreeID=3))              

  declare cur_newfeebase cursor for
    select * from @local_tab
  open cur_newfeebase 
  fetch next from cur_newfeebase
  into @freeid,@iemptypeid,@ideptid,@ifilid
  while @@fetch_status = 0 
  begin
    select @icount=count(baseid) from tfd_wagefeebase
      where freeid=@freeid and deptid=@ideptid
        and emptypeid=@iemptypeid and filid=@ifilid
    if (@icount=0)
    begin
      select @icount=max(baseid) from tfd_wagefeebase
        where freeid=@freeid and filid=@ifilid    
      set @icount=ISNULL(@icount, 0)+1
      insert into tfd_wagefeebase (baseid,freeid,deptid,emptypeid,filid)
        values (@icount,@freeid,@ideptid,@iemptypeid,@ifilid);
    end
    fetch next from cur_newfeebase
    into @freeid,@iemptypeid,@ideptid,@ifilid  
  end
  close cur_newfeebase
  deallocate cur_newfeebase
end